﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using RN.ERP.Entity;
using RN.ERP.Entity.QD;
using System.Data.Entity;
using Fly.Log;

namespace RN.ERP.DAL
{
    public class ChangeDAL
    {
        QD_Context dbContext = new QD_Context();

        public ChangeDAL(DbContext dbContext)
        {
            this.dbContext = (QD_Context)dbContext;
        }

        public IList<V_ChangeInfo> GetChanges(string objectType, string corpUserGuid, string flowCode, string condition, int pageIndex, int pageSize, out int count)
        {
            string strSql = "select * from v_changeinfo where IsDel <> 1   ";
            if (!string.IsNullOrEmpty(objectType) && !objectType.Equals("0"))
            {
                if (objectType.Equals("1"))
                {
                    strSql += "and objecttype in('1','3') ";
                }
                else if (objectType.Equals("4"))
                {
                    strSql += "and objecttype in('4','6') ";
                }
                else if (objectType == "8")
                {
                    strSql += "and objecttype in('1,7','7,1','1','7') ";
                }
                else
                {
                    strSql += "and objecttype='" + objectType + "' ";
                }
            }
            if (!string.IsNullOrEmpty(corpUserGuid))
            {
                strSql += " and isrtUserId=@isrtUserId ";
            }
            if (!string.IsNullOrEmpty(flowCode))
            {
                if (flowCode.Equals("0,019,"))
                {
                    strSql += " and (flowcode not like'1%' and flowcode not like'%,021%' and flowcode <>'0') ";
                }
                else
                    if (flowCode != "0")
                    {
                        flowCode = flowCode.TrimEnd(',');
                        strSql += " and flowcode  like '" + flowCode + "%'  ";
                    }
                    else
                    {
                        strSql += " and flowcode=@flowcode ";
                    }
            }
            if (!string.IsNullOrEmpty(condition))
            {
                strSql += condition;
            }
            strSql += "  ORDER BY IsrtDate desc ";
            count = dbContext.Database.SqlQuery<V_ChangeInfo>(strSql, new SqlParameter[] { 
                new SqlParameter("isrtUserId",corpUserGuid),
                new SqlParameter("flowcode",flowCode)
            }).ToList().Count;
            return dbContext.Database.SqlQuery<V_ChangeInfo>(strSql, new SqlParameter[] { 
                new SqlParameter("isrtUserId",corpUserGuid),
                new SqlParameter("flowcode",flowCode)
            }).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
        }

        //public int GetChangesCount(string objectType, string corpUserGuid, string flowCode, string condition, int pageIndex, int pageSize)
        //{
        //    string strSql = "select * from v_changeinfo where isdel=0 ";
        //    if (!string.IsNullOrEmpty(objectType) && !objectType.Equals("0"))
        //    {
        //        if (objectType.Equals("1"))
        //        {
        //            strSql += "and objecttype in('1','3') ";
        //        }
        //        else if (objectType.Equals("4"))
        //        {
        //            strSql += "and objecttype in('4','6') ";
        //        }
        //        else
        //        {
        //            strSql += "and objecttype='" + objectType + "' ";
        //        }
        //    }
        //    if (!string.IsNullOrEmpty(corpUserGuid))
        //    {
        //        strSql += " and isrtUserId=@isrtUserId ";
        //    }
        //    if (!string.IsNullOrEmpty(flowCode))
        //    {
        //        strSql += " and flowcode=@flowcode ";
        //    }
        //    if (!string.IsNullOrEmpty(condition))
        //    {
        //        strSql += condition;
        //    }
        //    strSql += "  ORDER BY IsrtDate desc ";
        //    return dbContext.Database.SqlQuery<V_ChangeInfo>(strSql, new SqlParameter[] { 
        //        new SqlParameter("isrtUserId",corpUserGuid),
        //        new SqlParameter("flowcode",flowCode)
        //    }).ToList().Count;
        //}

        public bool DeleteChanges(int changeSetId)
        {
            string strSql = "delete from tb_changeRecord where changesetid=@changeSetId ";
            dbContext.Database.ExecuteSqlCommand(strSql, new SqlParameter("changeSetId", changeSetId));
            return true;
        }
        /// <summary>
        /// 获取指定变更集的详细信息，包括变更记录，变更规则，审核状态等
        /// </summary>
        /// <param name="changeSetId"></param>
        /// <returns></returns>
        public List<ChangeItemRecordInfo> GetChangeItemRecordInfo(int changeSetId)
        {
            return dbContext.Database.SqlQuery<ChangeItemRecordInfo>(@"SELECT  
r.ID AS RecordID,
r.EmpGuid,
r.ChangeSetID,
r.BeforeChange,
r.AfterChange,
r.Remark,
r.OptDate,
r.BeforeValue,
r.AfterValue,
f.FlowCode,
s.ObjectId,
i.ID AS ItemID,
i.ClassID,
i.ChangeItem,
i.ChangeItemName,
i.ClassValue,
i.RegularExpression,
i.UpdateTable,
i.UpdateColumn,
i.SortCode,
i.BindSQL,
i.UploadID,
i.IsDel,
i.ItemType,
i.BeforeSQL, 
i.SelectType
FROM    dbo.Tb_ChangeSetRecord s 
INNER JOIN dbo.Tb_FlowCode f ON s.Id = f.ObjectId AND f.CodeType = 8020 AND  f.IsDel <> 1  
INNER JOIN dbo.tb_ChangeRecord  r ON r.ChangeSetID=s.Id 
INNER JOIN dbo.tb_ChangeItem i ON r.ChangeItem=i.ChangeItem WHERE s.Id=@SetID AND i.IsDel <> 1   and i.classid=8020  ", new SqlParameter[] { new SqlParameter("SetID", changeSetId) }).ToList();
        }

        public List<User_Log> AuditLogs(string objectId)
        {
            List<User_Log> logs = null;
            if (!string.IsNullOrWhiteSpace(objectId))
            {
                string sql = string.Format("SELECT * FROM User_Log WHERE ObjectId='{0}' AND Code='8020' and AppID='98' AND (FunctionId is not Null AND FunctionId>2) ", objectId.Trim());
                IEnumerable<User_Log> list = dbContext.Database.SqlQuery<User_Log>(sql, new SqlParameter[] { });
                if (list != null)
                {
                    logs = list.ToList();
                }

            }
            return logs;
        }
    }
}
